﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DLL
{
    public class DAHD_MA_DVI
    {
                // Chuỗi kết nối
        string strConnection;

        // SqlConnection
        SqlConnection sqlConn = null;
        private StringBuilder sbCommand = new StringBuilder(String.Empty);

        // Ten bang
        private const string c_table = "HD_MA_DVI";

        // Ten cot cua bang Don Vi
        private const string c_ma_dvi = "ma_dvi";
        private const string c_ten = "ten";
        private const string c_cap = "cap";
        private const string c_dia_chi = "dia_chi";

        public DAHD_MA_DVI(string strConnection)
        {
            this.strConnection = strConnection;
        }

        private void OpenConnection()
        {
            sqlConn = new SqlConnection(strConnection);
            sqlConn.Open();
        }

        private void CloseConnection()
        {
            sqlConn.Close();
        }

        public DataSet GetThongTinDonVi(int ma_dvi)
        {
            DataSet ds = new DataSet();
            this.OpenConnection();
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendLine("SELECT  ma_dvi");
            sbCommand.AppendLine(",ten");
            sbCommand.AppendLine(",cap");
            sbCommand.AppendLine(",dia_chi");
            sbCommand.AppendFormat("FROM {0} DV", c_table).AppendLine();

            if (ma_dvi > 0)
            {
                sbCommand.AppendFormat("WHERE DV.ma_dvi = N'{0}'", ma_dvi).AppendLine();
            }

            SqlCommand cmd = new SqlCommand(sbCommand.ToString(), sqlConn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            this.CloseConnection();
            return ds;
        }

        public string GetMaDonVi(string strTenDV)
        {
            DataTable ds = new DataTable();
            this.OpenConnection();
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendLine("SELECT TOP(1) ma_dvi AS MA");
            sbCommand.AppendFormat("FROM {0} DV", c_table).AppendLine();

            if (!string.IsNullOrEmpty(strTenDV))
            {
                sbCommand.AppendFormat("WHERE DV.TEN = N'{0}'", strTenDV).AppendLine();
            }

            SqlCommand cmd = new SqlCommand(sbCommand.ToString(), sqlConn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            this.CloseConnection();
            return ds.Rows[0]["MA"].ToString();
        }

        private bool IsExistDonVi(BLL.BOHD_MA_DVI bOHD_MA_DVI, string strConnection)
        {
            sbCommand = new StringBuilder(String.Empty);
            string ma_dvi = bOHD_MA_DVI.Ma_dvi.ToString();
            sbCommand.AppendFormat("SELECT * FROM {0} WHERE {1} = @contract_id", c_table, c_ma_dvi).AppendLine();
            SqlConnection sqlConnection = new SqlConnection(strConnection);
            SqlDataAdapter da = new SqlDataAdapter(sbCommand.ToString(), sqlConnection);
            da.SelectCommand.Parameters.AddWithValue("@ma_dvi", ma_dvi);
            DataTable dataTable = new DataTable();
            da.Fill(dataTable);

            if (dataTable.Rows.Count < 1)
            {
                // Hop dong chua ton tai
                return false;
            }
            else
            {
                // Hop Dong da ton tai
                return true;
            }
        }
    }
}
